/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.UserGroup;

/**
 *
 * @author modesto
 */
public class UserGroupDAO extends DAO {
    
    public UserGroupDAO() throws ClassNotFoundException, SQLException {
        super();
    }
    
    public void add (UserGroup ug) throws SQLException {
        String sql = "INSERT INTO usergroup VALUES (NULL, ?)";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        
        stmt.setString(1, ug.getName());
        
        stmt.executeUpdate();

        ResultSet rs = stmt.getGeneratedKeys();
        
        rs.first();
        
        ug.setId(rs.getInt(1));
        
    }
    
    public int remove (UserGroup ug) throws SQLException {
        String sql = "DELETE FROM usergroup WHERE id = ?";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql);
        
        stmt.setInt(1, ug.getId());
        
        return stmt.executeUpdate();
    }
    
    
    public int update (UserGroup ug) throws SQLException {
        String sql = "UPDATE usergroup SET name = ? WHERE id = ?";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql);
        
        stmt.setString(1, ug.getName());
        stmt.setInt(2, ug.getId());
        
        return stmt.executeUpdate();
    }
    
    public UserGroup getById (Integer id) throws SQLException {
        UserGroup ug = null;
        String sql = "SELECT * FROM usergroup WHERE id = ?";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql);
        
        stmt.setInt(1, id);
        
        ResultSet rs = stmt.executeQuery();
        
        if (rs.first()) {
            ug = new UserGroup();
            ug.setId(rs.getInt("id"));
            ug.setName(rs.getString("name"));
        }
        
        return ug;
        
    }
    
    
    public ArrayList<UserGroup> getAll() throws SQLException {
        ArrayList<UserGroup> userGroupList = new ArrayList<>();
        String sql = "SELECT * FROM usergroup";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql);
        
        ResultSet rs = stmt.executeQuery();
        
        while (rs.next()) {
            UserGroup ug = new UserGroup();
            ug.setId(rs.getInt("id"));
            ug.setName(rs.getString("name"));
            userGroupList.add(ug);
        }
        
        return userGroupList;
    }
}
